package com.ztc.service;

import com.ztc.beans.Book;
import com.ztc.beans.Jbook;
import com.ztc.beans.User;
import com.ztc.util.JDBCUtil;
import com.ztc.util.Page;
import org.apache.ibatis.session.SqlSession;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookDao {

    //查询书籍列表
 public List<Book> getbook(){
     String sql  = "select * from book";
     List<Book> slist = new ArrayList<>();
     Connection conn = null;
     PreparedStatement ps = null;
     ResultSet rs = null;
     try {

         //连接数据库
         conn = JDBCUtil.getConnection();
         //创建执行sql的对象,预加载sql
         ps= conn.prepareStatement(sql);
         rs = ps.executeQuery();
         while(rs.next()){
            Book book =new Book();
            book.setId(rs.getInt("id"));
            book.setName(rs.getString("bname"));
            book.setAuthor(rs.getString("author"));
            book.setBsum(rs.getInt("bsum"));
            book.setPrice(rs.getDouble("bprice"));
            slist.add(book);
        }

     }catch (SQLException e) {
         throw new RuntimeException(e);
     }finally{
         //关闭连接
         JDBCUtil.close(conn,ps,rs);
     }
     return slist;
 }


    //加入图书
    public void addBoook(Book book){
      /*  SqlSession sqlSession = MybatisUtil.getSqlSession();
        com.ztc.dao.BookDao bookDao1 = sqlSession.getMapper(com.ztc.dao.BookDao.class);
        bookDao1.addBoook(book);
        MybatisUtil.close(sqlSession);*/


        String sql  = "insert into book values(null,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setString(1,book.getName());
            ps.setString(2,book.getAuthor());
            ps.setInt(3,book.getBsum());
            ps.setDouble(4,book.getPrice());
            ps.executeUpdate();
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }


    }

    //查询图书是否存在
    public boolean getBookName(String name) {
        String sql = "select * from book where bname = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while(rs.next()){

                return true;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return false;
    }
    public boolean getBookName2(String name) {
        String sql = "select * from book where bname = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while(rs.next()){

                return true;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return false;
    }



    public void upbookBsum1(int id) {
        String sql = "update book set bsum = bsum - 1 where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
     }



    //判断书名是否已存在
    public Book getBookById(int id) {
        String sql  = "select * from book where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps= conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                Book book =new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("bname"));
                book.setAuthor(rs.getString("author"));
                book.setBsum(rs.getInt("bsum"));
                book.setPrice(rs.getDouble("bprice"));
                return book;
            }

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return null;

    }



    //修改(功能)书籍信息
    public void upBook(int id ,Book book) {
        String sql  = "update book set bname = ?,author = ?,bsum = ?,bprice = ? where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setString(1,book.getName());
            ps.setString(2,book.getAuthor());
            ps.setInt(3,book.getBsum());
            ps.setDouble(4,book.getPrice());
            ps.setInt(5,id);
            ps.executeUpdate();

        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
    }

    //删除书籍
    public void delBook(int id) {
        String sql = "delete from book where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
            System.out.println("删除成功！");
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
    }


    //判断id是否已存在
    public Book getBookById1(int id) {
        String sql  = "select * from book where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps= conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                Book book =new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("bname"));
                book.setAuthor(rs.getString("author"));
                book.setBsum(rs.getInt("bsum"));
                book.setPrice(rs.getDouble("bprice"));
                return book;
            }

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return null;
    }

    //判断书名是否已存在
    public Book getBookById(String name) {
        String sql  = "select * from book where bname=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps= conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while(rs.next()){
                Book book =new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("bname"));
                book.setAuthor(rs.getString("author"));
                book.setBsum(rs.getInt("bsum"));
                book.setPrice(rs.getDouble("bprice"));
                return book;
            }

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return null;

    }
    //修改(功能)书籍信息
    public void sertBook(Book book) {
        String sql  = "insert into book values (?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,book.getId());
            ps.setString(2,book.getName());
            ps.setString(3,book.getAuthor());
            ps.setInt(4,book.getBsum());
            ps.setDouble(5,book.getPrice());
            ps.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
    }


    public void addjbook(int id,String bid) {
        String sql  = "insert into jbook(id,uid,bid,jdate) values (null,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            Jbook book = new Jbook();
            ps.setInt(1,id);
            ps.setInt(2,Integer.parseInt(bid));
            ps.setTimestamp(3,new Timestamp(new Date().getTime()));
            ps.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }




    }
    public boolean getjbook(String s) {
        String sql  = "select * from jbook where id=? and hdate is null";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(s));
            rs = ps.executeQuery();
            while(rs.next()){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return false;


    }






    public boolean getjbook(int id, String bid) {
        String sql  = "select * from jbook where uid=? and bid=? and hdate is null";
        Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;

        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setInt(2,Integer.parseInt(bid));
            rs = ps.executeQuery();
            while(rs.next()){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return false;


    }

    public List<Jbook> getjbooks( int id) {
        String sql = "SELECT j.*,u.uname uname,b.bname bname FROM jbook j LEFT JOIN USER u ON j.uid=u.id\n" +
                "\t\t\t                LEFT JOIN book b ON j.bid=b.id where j.uid=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Jbook> list = new ArrayList<Jbook>();
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                Jbook jbook =new Jbook();
                jbook.setId(rs.getInt("id"));
                jbook.setUid(rs.getInt("uid"));
                jbook.setBid(rs.getInt("bid"));
                jbook.setJdate(rs.getTimestamp("jdate"));
                jbook.setHdate(rs.getTimestamp("hdate"));
                User user = new User();
                user.setName(rs.getString("uname"));
                jbook.setUser(user);
                Book book = new Book();
                book.setName(rs.getString("bname"));
                jbook.setBook(book);
                list.add(jbook);
            }

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
        return  list;
    }

    public void upjbook(String id) {
        String sql = "update jbook set hdate=? where id=?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Jbook> list = new ArrayList<Jbook>();
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setTimestamp(1,new Timestamp(new Date().getTime()));
            ps.setInt(2,Integer.parseInt(id));
            ps.execute();

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
    }

    public void upbookBsum2(String bid) {
        String sql = "update book set bsum = bsum + 1 where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(bid));
            ps.executeUpdate();

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            //关闭连接
            JDBCUtil.close(conn,ps,rs);
        }
    }

    public List<Book> getbooks(String bname, String author, Page page) {
        String sql = "select * from book where 1=1";
        List<Book> blist = new ArrayList<>();
        if(bname!=null&&!bname.equals("")){
            sql+=" and bname like '%"+bname+"%'";
        }
        if(author!=null&&!author.equals("")){
            sql+=" and author like '%"+author+"%'";
        }
        sql += " limit "+page.getPageSize()*(page.getCurrentPage()-1)+","+page.getPageSize();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        System.out.println(sql);
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Book book =new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("bname"));
                book.setAuthor(rs.getString("author"));
                book.setBsum(rs.getInt("bsum"));
                book.setPrice(rs.getDouble("bprice"));
                blist.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return blist;

    }
    public List<Book> getbooks(Page page) {
        String sql = "select * from book where 1=1";
        List<Book> blist = new ArrayList<>();
        sql += " limit "+page.getPageSize()*(page.getCurrentPage()-1)+","+page.getPageSize();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        System.out.println(sql);
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Book book =new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("bname"));
                book.setAuthor(rs.getString("author"));
                book.setBsum(rs.getInt("bsum"));
                book.setPrice(rs.getDouble("bprice"));
                blist.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return blist;

    }

    public int getBookSum(String bname, String author) {
        String sql = "select count(*) c from book where 1=1";
        if(bname!=null&&!bname.equals("")){
            sql+=" and bname like '%"+bname+"%'";
        }
        if(author!=null&&!author.equals("")){
            sql+=" and author like '%"+author+"%'";
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        System.out.println(sql);
        try {
            //连接数据库
            conn = JDBCUtil.getConnection();
            //创建执行sql的对象,预加载sql
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
               return rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
